﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_BuaantrongngayDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        //ClsConnection ClsCnn = new ClsConnection();
        ClsTblDm_NamhocDAL ClsTblDm_Namhocdal = new ClsTblDm_NamhocDAL();
        static string iNamhocId;
        public ClsTblDm_BuaantrongngayDAL()
        {
            iNamhocId = ClsTblDm_Namhocdal.ReturnNamhocId().ToString();
        }
        public DataSet FindSameName(string VarTen)
        {
            string NamePrc = QueryString.TBLDM_BUAANTRONGNGAY_FINDNAMESAMEPRC;
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@VarTen", VarTen));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset;
        }

        public DataSet GetById(int Id)
        {
            DataSet Set = new DataSet();
            string NamePrc = QueryString.SP_BUAANTRONGNGAY_GETBYID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Id", Id));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set);
            return Set;
        }

        public BindingSource SelectAll()
        {
            string NamePrc = QueryString.TBLDM_BUAANTRONGNGAY_SELECTALL_PRC;
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);

            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);

            return Binding;

        }
        public BindingSource ThucdontuanSelectAllByKhoilopIdandDate(int KhoilopId, DateTime Value)
        {
            string NamePrc = "SELECT TblThu2.Id, TblThu2.Tenbuaan, TblThu2.thu2, TblThu3.thu3, TblThu4.thu4, TblThu5.thu5, TblThu6.thu6, TblThu7.thu7 " +
       " FROM [SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu7 " +
       " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
       "  FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 5, #" + Value.ToShortDateString() + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu7 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu6 " +
       " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\",4, #" + Value.ToShortDateString() + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu6 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu5 " +
       " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
       " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 3, #" + Value.ToShortDateString() + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu5 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu4 " +
   " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 2, #" + Value.ToShortDateString() + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu4 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu3 " +
   " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 1, #" + Value.ToShortDateString() + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu3 INNER JOIN [SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu2 " +
   " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=#" + Value.ToShortDateString() + "#  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu2 ON TblThu3.Id = TblThu2.Id) ON TblThu4.Id = TblThu2.Id) ON TblThu5.Id = TblThu2.Id) ON TblThu6.Id = TblThu2.Id) ON TblThu7.Id = TblThu2.Id ";
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);

            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);

            return Binding;

        }

        public void Insert(string Ten, int Phanbotienan)
        {
            string NamePrc = QueryString.TBLDM_BUAANTRONGNGAY_INSERTPRC;
            OleDbCommand Cmm = new OleDbCommand();

            OleDbParameter Ten_Paramet = new OleDbParameter();

            Ten_Paramet.ParameterName = "@Tenbuaan";
            Ten_Paramet.OleDbType = System.Data.OleDb.OleDbType.VarWChar;
            Ten_Paramet.Value = Ten;

            Cmm.Parameters.Add(Ten_Paramet);

            OleDbParameter Phanbotienan_Paramet = new OleDbParameter();

            Phanbotienan_Paramet.ParameterName = "@Phanbotienan";
            Phanbotienan_Paramet.OleDbType = OleDbType.Integer;
            Phanbotienan_Paramet.Value = Phanbotienan;

            Cmm.Parameters.Add(Phanbotienan_Paramet);
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Edit(int Id, string Ten, int Phanbotienan)
        {
            //string NamePrc = QueryString.TBLDM_BUAANTRONGNGAY_EDITPRC;
            string NamePrc = "update TblDm_Buaantrongngay set Tenbuaan ='" + Ten + "', Phanbotienan = " + Phanbotienan + " where Id=" + Id;
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
            Cmm.Connection.Close();
        }
        public void Del(int Id)
        {
            string NamePrc = QueryString.TBLDM_BUAANTRONGNGAY_DEL;
            OleDbCommand Cmm = new OleDbCommand();
            OleDbParameter Id_Paramet = new OleDbParameter();
            OleDbParameter Ten_Paramet = new OleDbParameter();

            Id_Paramet.ParameterName = "@Id";
            Id_Paramet.OleDbType = OleDbType.Integer;
            Id_Paramet.Value = Id;

            Cmm.Parameters.Add(Id_Paramet);

            ClsConnection.OleDbDAdpter_Del(NamePrc, Cmm);

        }
        public static DataTable GetAll()
        {
            string NamePrc = QueryString.TBLDM_BUAANTRONGNGAY_SELECTALL_PRC;

            return DataAccess.FillTable(ClsConnection.connectString, NamePrc, false);
        }
    }
}
